{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Congestion Charges - Hard\n",
    "\n",
    "You may need to create views to complete these questions - but you do not have permission to create tables or views in the default schema. Your SQL commands are executed by user scott in schema gisq - you may create or drop views and tables in schema scott but not in gisq."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "When creating a view in scott you must specify the schema name of the sources and the destination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE SCHEMA IF NOT EXISTS scott;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "There are four types of permit. The most popular type means that this type has been issued the highest number of times. Find out the most popular type, together with the total number of permits issued."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>chargetype</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Daily</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Daily', 27)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT chargetype, COUNT(*)\n",
    "  FROM permit\n",
    "    GROUP BY chargetype\n",
    "    ORDER BY COUNT(*) DESC\n",
    "    LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "For each of the vehicles caught by camera 19 - show the registration, the earliest time at camera 19 and the time and camera at which it left the zone."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>reg</th>\n",
       "        <th>earliest</th>\n",
       "        <th>next</th>\n",
       "        <th>camera</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 CSP</td>\n",
       "        <td>2007-02-25 07:51:10</td>\n",
       "        <td>2007-02-25 07:55:11</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 DSP</td>\n",
       "        <td>2007-02-25 16:31:01</td>\n",
       "        <td>2007-02-25 17:42:41</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 JSP</td>\n",
       "        <td>2007-02-25 17:14:11</td>\n",
       "        <td>2007-02-25 17:17:03</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 TSP</td>\n",
       "        <td>2007-02-25 07:23:00</td>\n",
       "        <td>2007-02-25 07:26:31</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SO 02 CSP', datetime.datetime(2007, 2, 25, 7, 51, 10), datetime.datetime(2007, 2, 25, 7, 55, 11), 18),\n",
       " ('SO 02 DSP', datetime.datetime(2007, 2, 25, 16, 31, 1), datetime.datetime(2007, 2, 25, 17, 42, 41), 19),\n",
       " ('SO 02 JSP', datetime.datetime(2007, 2, 25, 17, 14, 11), datetime.datetime(2007, 2, 25, 17, 17, 3), 3),\n",
       " ('SO 02 TSP', datetime.datetime(2007, 2, 25, 7, 23), datetime.datetime(2007, 2, 25, 7, 26, 31), 19)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "  -- registrations showing at camera 19, the earliest time\n",
    "  SELECT reg, MIN(whn) earliest\n",
    "    FROM image JOIN vehicle ON (image.reg=vehicle.id)\n",
    "      WHERE image.camera=19\n",
    "      GROUP BY reg\n",
    ")\n",
    "SELECT a.reg, a.earliest, a.next, image.camera FROM\n",
    " (SELECT t.reg, t.earliest, MIN(image.whn) AS next\n",
    "  FROM t LEFT JOIN image ON (\n",
    "      t.earliest<image.whn AND t.reg=image.reg)\n",
    "  GROUP BY t.reg, t.earliest) a JOIN image ON (\n",
    "    a.reg=image.reg AND a.next=image.whn);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "For all 19 cameras - show the position as IN, OUT or INTERNAL and the busiest hour for that camera."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "28 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>camera</th>\n",
       "        <th>type</th>\n",
       "        <th>hr</th>\n",
       "        <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>IN</td>\n",
       "        <td>6.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>IN</td>\n",
       "        <td>7.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>IN</td>\n",
       "        <td>18.0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>IN</td>\n",
       "        <td>17.0</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>IN</td>\n",
       "        <td>7.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>IN</td>\n",
       "        <td>7.0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>OUT</td>\n",
       "        <td>18.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>OUT</td>\n",
       "        <td>16.0</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>OUT</td>\n",
       "        <td>6.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>OUT</td>\n",
       "        <td>5.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>OUT</td>\n",
       "        <td>18.0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>OUT</td>\n",
       "        <td>7.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>OUT</td>\n",
       "        <td>7.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>OUT</td>\n",
       "        <td>18.0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>OUT</td>\n",
       "        <td>17.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>OUT</td>\n",
       "        <td>7.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>OUT</td>\n",
       "        <td>18.0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>OUT</td>\n",
       "        <td>18.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>OUT</td>\n",
       "        <td>7.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>INTERNAL</td>\n",
       "        <td>7.0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">28 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[(1, 'IN', 6.0, 1),\n",
       " (2, 'IN', 7.0, 1),\n",
       " (3, 'IN', 18.0, 2),\n",
       " (3, 'IN', 17.0, 3),\n",
       " (5, 'IN', 7.0, 1),\n",
       " (8, 'IN', 7.0, 2),\n",
       " (9, 'OUT', 18.0, 1),\n",
       " (9, 'OUT', 16.0, 6),\n",
       " (9, 'OUT', 6.0, 1),\n",
       " (10, 'OUT', 5.0, 1),\n",
       " (10, 'OUT', 18.0, 2),\n",
       " (10, 'OUT', 7.0, 1),\n",
       " (11, 'OUT', 7.0, 1),\n",
       " (11, 'OUT', 18.0, 2),\n",
       " (12, 'OUT', 17.0, 1),\n",
       " (12, 'OUT', 7.0, 1),\n",
       " (12, 'OUT', 18.0, 2),\n",
       " (15, 'OUT', 18.0, 1),\n",
       " (16, 'OUT', 7.0, 1),\n",
       " (17, 'INTERNAL', 7.0, 1),\n",
       " (17, 'INTERNAL', 6.0, 2),\n",
       " (18, 'INTERNAL', 17.0, 1),\n",
       " (18, 'INTERNAL', 6.0, 1),\n",
       " (18, 'INTERNAL', 7.0, 3),\n",
       " (18, 'INTERNAL', 16.0, 3),\n",
       " (19, 'INTERNAL', 7.0, 4),\n",
       " (19, 'INTERNAL', 17.0, 2),\n",
       " (19, 'INTERNAL', 16.0, 1)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "  SELECT camera, COALESCE(camera.perim, 'INTERNAL') AS type,\n",
    "    EXTRACT (HOUR FROM image.whn) hr, COUNT(*) n\n",
    "      FROM camera JOIN image ON (camera.id=image.camera)\n",
    "      GROUP BY camera, type, hr\n",
    ")\n",
    "SELECT camera, type, hr, MAX(n)\n",
    "  FROM t\n",
    "    GROUP BY camera, type, hr\n",
    "    ORDER BY camera;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "Anomalous daily permits. Daily permits should not be issued for non-charging days. Find a way to represent charging days. Identify the anomalous daily permits."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>reg</th>\n",
       "        <th>sdate</th>\n",
       "        <th>chargetype</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 ATP</td>\n",
       "        <td>2007-01-21 00:00:00</td>\n",
       "        <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 BTP</td>\n",
       "        <td>2007-02-03 00:00:00</td>\n",
       "        <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 BTP</td>\n",
       "        <td>2007-02-04 00:00:00</td>\n",
       "        <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 CTP</td>\n",
       "        <td>2007-01-21 00:00:00</td>\n",
       "        <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 FTP</td>\n",
       "        <td>2007-02-25 00:00:00</td>\n",
       "        <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 HTP</td>\n",
       "        <td>2006-01-21 00:00:00</td>\n",
       "        <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 HTP</td>\n",
       "        <td>2006-01-22 00:00:00</td>\n",
       "        <td>Daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 JTP</td>\n",
       "        <td>2007-01-21 00:00:00</td>\n",
       "        <td>Daily</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SO 02 ATP', datetime.datetime(2007, 1, 21, 0, 0), 'Daily'),\n",
       " ('SO 02 BTP', datetime.datetime(2007, 2, 3, 0, 0), 'Daily'),\n",
       " ('SO 02 BTP', datetime.datetime(2007, 2, 4, 0, 0), 'Daily'),\n",
       " ('SO 02 CTP', datetime.datetime(2007, 1, 21, 0, 0), 'Daily'),\n",
       " ('SO 02 FTP', datetime.datetime(2007, 2, 25, 0, 0), 'Daily'),\n",
       " ('SO 02 HTP', datetime.datetime(2006, 1, 21, 0, 0), 'Daily'),\n",
       " ('SO 02 HTP', datetime.datetime(2006, 1, 22, 0, 0), 'Daily'),\n",
       " ('SO 02 JTP', datetime.datetime(2007, 1, 21, 0, 0), 'Daily')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM permit\n",
    "  WHERE (EXTRACT(DOW FROM sdate) IN (0, 6))\n",
    "    AND chargetype='Daily';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "Issuing fines: Vehicles using the zone during the charge period, on charging days must be issued with fine notices unless they have a permit covering that day. List the name and address of such culprits, give the camera and the date and time of the first offence."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>reg</th>\n",
       "        <th>name</th>\n",
       "        <th>address</th>\n",
       "        <th>first_offence</th>\n",
       "        <th>camera</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "-- vehicles with permits\n",
    "  SELECT permit.reg, sdate, keeper,\n",
    "    (sdate + CASE WHEN chargetype='Daily' THEN INTERVAL '1 day'\n",
    "                  WHEN chargetype='Weekly' THEN INTERVAL '1 week'\n",
    "                  WHEN chargetype='Monthly' THEN INTERVAL '1 month'\n",
    "                  WHEN chargetype='Annual' THEN INTERVAL '1 year'\n",
    "             END) AS edate\n",
    "      FROM vehicle LEFT JOIN\n",
    "      permit ON (vehicle.id=permit.reg)\n",
    "), f AS (\n",
    "  SELECT image.reg, name, address, camera, whn\n",
    "    FROM t RIGHT JOIN image ON (t.reg=image.reg AND NOT\n",
    "                              (image.whn BETWEEN t.sdate AND t.edate)) JOIN\n",
    "      keeper ON (keeper.id=t.keeper)\n",
    "      WHERE (EXTRACT(DOW FROM whn) BETWEEN 1 AND 5)\n",
    ")\n",
    "SELECT a.reg, a.name, a.address, a.first_offence, b.camera FROM\n",
    " (SELECT reg, name, address, MIN(whn) first_offence FROM f \n",
    "  GROUP BY reg, name, address) a JOIN\n",
    "    (SELECT * FROM f) b ON (a.reg=b.reg AND a.first_offence=b.whn);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
